﻿using CsvHelper;
using NetTaste;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.Dynamic;
using System.Globalization;
using System.Text;

namespace Single.Npoi
{
    public static class NpoiHelper
    {
        public static DataTable ExcelToDataTable(string filePath, string tableName, int sheetIndex = 0)
        {
            string extName = Path.GetExtension(filePath);

            DataTable dt = new DataTable();

            if (!string.IsNullOrEmpty(tableName))
            {
                dt.TableName = tableName;
            }

            if (extName.Equals(".xls") || extName.Equals(".xlsx"))
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook workbook = null;

                    switch (extName)
                    {
                        case ".xls": workbook = new HSSFWorkbook(file); break;
                        case ".xlsx": workbook = new XSSFWorkbook(file); break;
                        default: throw new Exception("未能识别的文件类型");
                    }

                    ISheet sheet = workbook.GetSheetAt(sheetIndex);

                    //列头
                    foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells)
                    {
                        var cellName = item.ToString();

                        if (!string.IsNullOrEmpty(cellName))
                        {
                            dt.Columns.Add(cellName);
                        }
                    }

                    //写入内容
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                    while (rows.MoveNext())
                    {
                        IRow row = (IRow)rows.Current;

                        if (row.RowNum == sheet.FirstRowNum)
                        {
                            continue;
                        }

                        DataRow dr = dt.NewRow();

                        foreach (ICell item in row.Cells)
                        {
                            if (item.ColumnIndex >= dt.Columns.Count) { continue; }

                            switch (item.CellType)
                            {
                                case CellType.Boolean:
                                    dr[item.ColumnIndex] = item.BooleanCellValue;
                                    break;
                                case CellType.Error:
                                    dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                    break;
                                case CellType.Formula:
                                    switch (item.CachedFormulaResultType)
                                    {
                                        case CellType.Boolean:
                                            dr[item.ColumnIndex] = item.BooleanCellValue;
                                            break;
                                        case CellType.Error:
                                            dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                            break;
                                        case CellType.Numeric:
                                            if (DateUtil.IsCellDateFormatted(item))
                                            {
                                                dr[item.ColumnIndex] = item.DateCellValue?.ToString("yyyy-MM-dd hh:MM:ss");
                                            }
                                            else { dr[item.ColumnIndex] = item.NumericCellValue; }
                                            break;
                                        case CellType.String:
                                            string str = item.StringCellValue;
                                            if (!string.IsNullOrEmpty(str))
                                            {
                                                dr[item.ColumnIndex] = str.ToString();
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = null;
                                            }
                                            break;
                                        case CellType.Unknown:
                                        case CellType.Blank:
                                        default:
                                            dr[item.ColumnIndex] = string.Empty;
                                            break;
                                    }
                                    break;
                                case CellType.Numeric:
                                    if (DateUtil.IsCellDateFormatted(item))
                                    {
                                        dr[item.ColumnIndex] = item.DateCellValue?.ToString("yyyy-MM-dd hh:MM:ss");
                                    }
                                    else { dr[item.ColumnIndex] = item.NumericCellValue; }
                                    break;
                                case CellType.String:
                                    string strValue = item.StringCellValue;
                                    if (!string.IsNullOrEmpty(strValue))
                                    {
                                        dr[item.ColumnIndex] = strValue.ToString();
                                    }
                                    else { dr[item.ColumnIndex] = null; }
                                    break;
                                case CellType.Unknown:
                                case CellType.Blank:
                                default: dr[item.ColumnIndex] = string.Empty; break;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }

            return dt;
        }

        public static DataTable ExcelToDataTableAutoColumn(string filePath, int sheetIndex)
        {
            string extName = Path.GetExtension(filePath);

            DataTable dt = new DataTable();

            if (extName.Equals(".xls") || extName.Equals(".xlsx"))
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook workbook = null;

                    switch (extName)
                    {
                        case ".xls": workbook = new HSSFWorkbook(file); break;
                        case ".xlsx": workbook = new XSSFWorkbook(file); break;
                        default: throw new Exception("未能识别的文件类型");
                    }

                    ISheet sheet = workbook.GetSheetAt(sheetIndex);

                    //列头
                    foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells)
                    {
                        dt.Columns.Add($"column_{item.ColumnIndex}", typeof(string));
                    }

                    //写入内容
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                    while (rows.MoveNext())
                    {
                        IRow row = (IRow)rows.Current;

                        DataRow dr = dt.NewRow();

                        foreach (ICell item in row.Cells)
                        {
                            switch (item.CellType)
                            {
                                case CellType.Boolean:
                                    dr[item.ColumnIndex] = item.BooleanCellValue;
                                    break;
                                case CellType.Error:
                                    dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                    break;
                                case CellType.Formula:
                                    switch (item.CachedFormulaResultType)
                                    {
                                        case CellType.Boolean:
                                            dr[item.ColumnIndex] = item.BooleanCellValue;
                                            break;
                                        case CellType.Error:
                                            dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                            break;
                                        case CellType.Numeric:
                                            if (DateUtil.IsCellDateFormatted(item))
                                            {
                                                dr[item.ColumnIndex] = item.DateCellValue?.ToString("yyyy-MM-dd hh:MM:ss");
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = item.NumericCellValue;
                                            }
                                            break;
                                        case CellType.String:
                                            string str = item.StringCellValue;
                                            if (!string.IsNullOrEmpty(str))
                                            {
                                                dr[item.ColumnIndex] = str.ToString();
                                            }
                                            else
                                            {
                                                dr[item.ColumnIndex] = null;
                                            }
                                            break;
                                        case CellType.Unknown:
                                        case CellType.Blank:
                                        default:
                                            dr[item.ColumnIndex] = string.Empty;
                                            break;
                                    }
                                    break;
                                case CellType.Numeric:
                                    if (DateUtil.IsCellDateFormatted(item))
                                    {
                                        dr[item.ColumnIndex] = item.DateCellValue?.ToString("yyyy-MM-dd hh:MM:ss");
                                    }
                                    else
                                    {
                                        dr[item.ColumnIndex] = item.NumericCellValue;
                                    }
                                    break;
                                case CellType.String:
                                    string strValue = item.StringCellValue;
                                    if (!string.IsNullOrEmpty(strValue))
                                    {
                                        dr[item.ColumnIndex] = strValue.ToString();
                                    }
                                    else
                                    {
                                        dr[item.ColumnIndex] = null;
                                    }
                                    break;
                                case CellType.Unknown:
                                case CellType.Blank:
                                default:
                                    dr[item.ColumnIndex] = string.Empty;
                                    break;
                            }
                        }

                        dt.Rows.Add(dr);
                    }
                }
            }

            return dt;
        }

        public static List<T> ExcelToListAutoColumn<T>(string filePath, int sheetIndex, int skip, string prefix, Action<T> action, Action<List<string>> setKeys) where T : class, new()
        {
            string extName = Path.GetExtension(filePath);

            var list = new List<T>();

            if (extName.Equals(".xls") || extName.Equals(".xlsx"))
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    IWorkbook workbook = null;

                    switch (extName)
                    {
                        case ".xls": workbook = new HSSFWorkbook(file); break;
                        case ".xlsx": workbook = new XSSFWorkbook(file); break;
                        default: throw new Exception("未能识别的文件类型");
                    }

                    ISheet sheet = workbook.GetSheetAt(sheetIndex);

                    //写入内容
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                    var t = typeof(T);

                    int index = 0;
                    var keys = new List<string>();

                    while (rows.MoveNext())
                    {
                        if (index < skip) { index++; continue; }

                        IRow row = (IRow)rows.Current;

                        var obj = new T();

                        foreach (ICell item in row.Cells)
                        {
                            var p = t.GetProperty($"{prefix}{item.ColumnIndex}");

                            if (p != null)
                            {
                                switch (item.CellType)
                                {
                                    case CellType.Boolean:
                                        p.SetValue(obj, item.BooleanCellValue.ToString());
                                        break;
                                    case CellType.Error:
                                        p.SetValue(obj, ErrorEval.GetText(item.ErrorCellValue));
                                        break;
                                    case CellType.Formula:
                                        switch (item.CachedFormulaResultType)
                                        {
                                            case CellType.Boolean:
                                                p.SetValue(obj, item.BooleanCellValue.ToString());
                                                break;
                                            case CellType.Error:
                                                p.SetValue(obj, ErrorEval.GetText(item.ErrorCellValue));
                                                break;
                                            case CellType.Numeric:
                                                if (DateUtil.IsCellDateFormatted(item))
                                                {
                                                    p.SetValue(obj, item.DateCellValue?.ToString("yyyy-MM-dd hh:MM:ss"));
                                                }
                                                else
                                                {
                                                    p.SetValue(obj, item.NumericCellValue.ToString());
                                                }
                                                break;
                                            case CellType.String:
                                                string str = item.StringCellValue;
                                                if (!string.IsNullOrEmpty(str))
                                                {
                                                    p.SetValue(obj, str.ToString());
                                                }
                                                else
                                                {
                                                    p.SetValue(obj, null);
                                                }
                                                break;
                                            case CellType.Unknown:
                                            case CellType.Blank:
                                            default:
                                                p.SetValue(obj, string.Empty);
                                                break;
                                        }
                                        break;
                                    case CellType.Numeric:
                                        if (DateUtil.IsCellDateFormatted(item))
                                        {
                                            p.SetValue(obj, item.DateCellValue?.ToString("yyyy-MM-dd hh:MM:ss"));
                                        }
                                        else
                                        {
                                            p.SetValue(obj, item.NumericCellValue.ToString());
                                        }
                                        break;
                                    case CellType.String:
                                        string strValue = item.StringCellValue;
                                        if (!string.IsNullOrEmpty(strValue))
                                        {
                                            p.SetValue(obj, strValue.ToString());
                                        }
                                        else
                                        {
                                            p.SetValue(obj, null);
                                        }
                                        break;
                                    case CellType.Unknown:
                                    case CellType.Blank:
                                    default:
                                        p.SetValue(obj, string.Empty);
                                        break;
                                }

                                if (index == skip)
                                {
                                    keys.Add(p.GetValue(obj)?.ToString() ?? string.Empty);
                                }
                            }
                            else { break; }
                        }

                        if (index == skip) { setKeys(keys); index++; continue; }

                        action(obj); list.Add(obj);
                    }
                }
            }

            return list;
        }

        public static void DataTableToExcel(DataTable dt, string sheetName, string filename)
        {
            string extName = Path.GetExtension(filename);

            //创建Excel
            IWorkbook book = null;

            switch (extName)
            {
                case ".xls": book = new HSSFWorkbook(); break;
                case ".xlsx": book = new XSSFWorkbook(); break;
                default: throw new Exception("未能识别的文件类型");
            }

            //创建工作薄
            ISheet sheet = book.CreateSheet(sheetName);

            //格式
            ICellStyle dateStyle = book.CreateCellStyle();
            IDataFormat format = book.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
            dateStyle.Alignment = HorizontalAlignment.CenterSelection;
            dateStyle.VerticalAlignment = VerticalAlignment.Center;

            ICellStyle cellStyle = book.CreateCellStyle();
            cellStyle.Alignment = HorizontalAlignment.CenterSelection;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;

            //创建标题行
            IRow row = sheet.CreateRow(0);

            ICell cell = null;

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                cell = row.CreateCell(i);

                cell.CellStyle = cellStyle;//格式化显示

                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            //创建内容行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                row = sheet.CreateRow(i + 1);

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    string drValue = dt.Rows[i][j].ToString();

                    switch (dt.Rows[i][j].GetType().ToString())
                    {
                        case "System.String"://字符串类型
                            if (drValue.Length > 32767)
                            {
                                drValue = drValue.Substring(0, 32767);
                            }
                            cell.SetCellValue(drValue);
                            cell.CellStyle = cellStyle;
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            cell.SetCellValue(dateV);
                            cell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            cell.SetCellValue(boolV);
                            cell.CellStyle = cellStyle;
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            cell.SetCellValue(intV);
                            cell.CellStyle = cellStyle;
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            cell.SetCellValue(doubV);
                            cell.CellStyle = cellStyle;
                            break;
                        case "System.DBNull"://空值处理
                            cell.SetCellValue("");
                            cell.CellStyle = cellStyle;
                            break;
                        default:
                            cell.SetCellValue("");
                            cell.CellStyle = cellStyle;
                            break;
                    }
                }
            }

            using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write)) { book.Write(fs); }
        }

        public static void DataTableToExcel(List<DataTable> dtList, List<string> sheetNameList, string filename)
        {
            string extName = Path.GetExtension(filename);

            //创建Excel
            IWorkbook book = null;

            switch (extName)
            {
                case ".xls": book = new HSSFWorkbook(); break;
                case ".xlsx": book = new XSSFWorkbook(); break;
                default: throw new Exception("未能识别的文件类型");
            }

            for (int k = 0; k < dtList.Count; k++)
            {
                var dt = dtList[k];
                var sheetName = sheetNameList[k];

                //创建工作薄
                ISheet sheet = book.CreateSheet(sheetName);

                //格式
                ICellStyle dateStyle = book.CreateCellStyle();
                IDataFormat format = book.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
                dateStyle.Alignment = HorizontalAlignment.CenterSelection;
                dateStyle.VerticalAlignment = VerticalAlignment.Center;

                ICellStyle cellStyle = book.CreateCellStyle();
                cellStyle.Alignment = HorizontalAlignment.CenterSelection;
                cellStyle.VerticalAlignment = VerticalAlignment.Center;

                //创建标题行
                IRow row = sheet.CreateRow(0);

                ICell cell = null;

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    cell = row.CreateCell(i);

                    cell.CellStyle = cellStyle;//格式化显示

                    cell.SetCellValue(dt.Columns[i].ColumnName);
                }

                //创建内容行
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    row = sheet.CreateRow(i + 1);

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);

                        string drValue = dt.Rows[i][j].ToString();

                        switch (dt.Rows[i][j].GetType().ToString())
                        {
                            case "System.String"://字符串类型
                                if (drValue.Length > 32767)
                                {
                                    drValue = drValue.Substring(0, 32767);
                                }
                                cell.SetCellValue(drValue);
                                cell.CellStyle = cellStyle;
                                break;
                            case "System.DateTime"://日期类型
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                cell.SetCellValue(dateV);
                                cell.CellStyle = dateStyle;//格式化显示
                                break;
                            case "System.Boolean"://布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                cell.SetCellValue(boolV);
                                cell.CellStyle = cellStyle;
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                cell.SetCellValue(intV);
                                cell.CellStyle = cellStyle;
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                cell.SetCellValue(doubV);
                                cell.CellStyle = cellStyle;
                                break;
                            case "System.DBNull"://空值处理
                                cell.SetCellValue("");
                                cell.CellStyle = cellStyle;
                                break;
                            default:
                                cell.SetCellValue("");
                                cell.CellStyle = cellStyle;
                                break;
                        }
                    }

                }

                using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write)) { book.Write(fs); }
            }
        }

        #region CVS

        public static DataTable CsvToDataTable(string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                using (StreamReader sr = new StreamReader(fs, Encoding.Default))
                {
                    using (CsvReader cr = new CsvReader(sr, CultureInfo.CurrentCulture))
                    {
                        var records = cr.GetRecords<dynamic>().ToList();

                        DataTable dt = new DataTable();

                        if (records.Count > 0)
                        {
                            IDictionary<string, object> id = records[0];

                            foreach (var key in id.Keys)
                            {
                                dt.Columns.Add(key);
                            }

                            foreach (var item in records)
                            {
                                IDictionary<string, object> idic = item;

                                DataRow row = dt.NewRow();

                                foreach (var key in id.Keys)
                                {
                                    row[key] = idic[key];
                                }

                                dt.Rows.Add(row);
                            }
                        }

                        return dt;
                    }
                }
            }
        }

        public static List<T> CsvToListAutoColumn<T>(string fileName, string prefix, Action<T> action, Action<List<string>> setKeys) where T : class, new()
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                using (StreamReader sr = new StreamReader(fs, Encoding.Default))
                {
                    using (CsvReader cr = new CsvReader(sr, CultureInfo.CurrentCulture))
                    {
                        var records = cr.GetRecords<dynamic>().ToList();

                        var list = new List<T>();

                        var t = typeof(T);

                        if (records.Count > 0)
                        {
                            IDictionary<string, object> id = records[0];

                            setKeys(id.Keys.ToList());

                            foreach (var item in records)
                            {
                                IDictionary<string, object> idic = item;

                                var obj = new T();

                                int index = 0;

                                foreach (var key in id.Keys)
                                {
                                    var p = t.GetProperty($"{prefix}{index++}");

                                    if (idic[key] != null)
                                    {
                                        p?.SetValue(obj, idic[key].ToString());
                                    }
                                }

                                action(obj); list.Add(obj);
                            }
                        }

                        return list;
                    }
                }
            }
        }

        public static void DataTableToCsv(DataTable dt, string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                using (StreamWriter sw = new StreamWriter(fs, Encoding.GetEncoding("gb2312")))
                {
                    using (CsvWriter cr = new CsvWriter(sw, CultureInfo.CurrentCulture))
                    {
                        var modelList = new List<dynamic>();

                        foreach (DataRow row in dt.Rows)
                        {
                            dynamic model = new ExpandoObject();

                            IDictionary<string, object> dict = model;

                            foreach (DataColumn column in dt.Columns)
                            {
                                dict[column.ColumnName] = $"'{row[column]}";
                            }
                            modelList.Add(model);
                        }

                        cr.WriteRecords(modelList);
                    }
                }
            }
        }

        #endregion
    }
}